package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.Grade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.respons.SchedulingGrade;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 班级 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-03-20
 */
public interface GradeMapper extends BaseMapper<Grade> {

    /**
     * 获取班级上课时间
     * @param schoolId
     * @param yearPart
     * @param qurarterNum
     * @return
     */
    @Select({"<script>",
            "select a.*,b.subjects_id,CONCAT(a.time_start, '-', a.time_end) AS school_time",
            "from grade as a left join course as b on a.course_id = b.id",
            "where 1 = 1",
            "and a.lesson_type = ${lessonType}",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and a.school_id = ${schoolId}",
            "</when>",
            "<when test='sourseLeve == 2'>",
            "and a.teacher_id = ${adminId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and b.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and a.year_part = ${yearPart}",
            "</when>",
            "<when test='qurarterNum!=null and qurarterNum!=&apos;&apos;'>",
            "and a.quarter_num = ${qurarterNum}",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos;'>",
            " and a.week_dates = #{weekDates}",
            "</when>",
            "and a.time_start is not null and a.time_end is not null and a.deleted=0  ORDER BY a.time_start asc ",
            "</script>"})
    public abstract List<SchedulingGrade> getGradeListForScheduling(@Param("schoolId") Long schoolId, @Param("subjectsId") Integer subjectsId, @Param("yearPart") Integer yearPart, @Param("qurarterNum") Integer qurarterNum, @Param("weekDates") String weekDates, @Param("lessonType") Integer lessonType, @Param("sourseLeve") Integer sourseLeve, @Param("adminId") Long adminId);

    /**
     * 获取班级上课时间
     * @param schoolId
     * @param yearPart
     * @param qurarterNum
     * @return
     */
    @Select({"<script>",
            "select CONCAT(a.time_start, '-', a.time_end) AS school_time",
            "from grade as a left join course as b on a.course_id = b.id",
            "where 1=1",
            "and a.lesson_type = ${lessonType},",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and a.school_id = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and b.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and a.year_part = ${yearPart}",
            "</when>",
            "<when test='qurarterNum!=null and qurarterNum!=&apos;&apos;'>",
            "and a.quarter_num = ${qurarterNum}",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos;'>",
            " and a.week_dates = #{weekDates}",
            "</when>",
            "and a.time_start is not null and a.time_end is not null and a.deleted=0 and b.deleted=0 ORDER BY a.time_start DESC LIMIT 1",
            "</script>"})
    public abstract String getGradeTime(@Param("schoolId") Long schoolId,@Param("subjectsId") Integer subjectsId, @Param("yearPart") Integer yearPart, @Param("qurarterNum") Integer qurarterNum,@Param("weekDates") String weekDates,@Param("lessonType") Integer lessonType);



    /**
     * 获取班级上课时间
     * @param schoolId
     * @param yearPart
     * @param qurarterNum
     * @return
     */
    @Select({"<script>",
            "select CONCAT(time_start, '-', time_end) AS school_time",
            "from grade",
            "where 1=1",
            "and lesson_type = ${lessonType}",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and school_id = ${schoolId}",
            "</when>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and year_part = ${yearPart}",
            "</when>",
            "<when test='qurarterNum!=null and qurarterNum!=&apos;&apos;'>",
            "and quarter_num = ${qurarterNum}",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos;'>",
            " and week_dates = #{weekDates}",
            "</when>",
            "and time_start is not null and time_end is not null and deleted=0  GROUP BY time_start,time_end ORDER BY time_start asc ",
            "</script>"})
    public abstract List<String> getSchoolTimeList(@Param("schoolId") Long schoolId,@Param("yearPart") Integer yearPart,@Param("qurarterNum") Integer qurarterNum,@Param("weekDates") String weekDates,@Param("lessonType") Integer lessonType);


    @Select({"<script>",
            "SELECT",
                    "*,",
                    "SUM( CASE WHEN student_grade_id IS NOT NULL THEN 1 ELSE 0 END ) AS student_count ",
                    "FROM",
                    "(",
                    "SELECT",
                      "t1.*,",
                      "t2.id AS student_grade_id ",
                    "FROM",
                      "grade AS t1",
                      "LEFT JOIN student_grade AS t2 ON t1.id = t2.grade_id ",
                      "AND (",
                        "t2.reading_status IN ( 0, 1 ) ",
                      "OR ( t2.use_course_count &gt; 0 )) ",
                      "AND t2.deleted = 0 ",
                      "AND t2.`status` &lt;&gt; 3 ",
                    "WHERE",
                        "t1.year_part = ${yearPart} ",
                        "AND t1.quarter_num = ${quarterNum} ",
                        "AND t1.school_id = ${schoolId} ",
                        "AND t1.subjects_id = ${subjectsId} ",
                        "AND t1.year_class_id = ${yearClassId} ",
                        "AND t1.lesson_type = ${lessonType} ",
                        "<when test='getType == 2'>",
                            "AND t1.course_type_id = ${classTypeId} ",
                        "</when>",
                        "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                        "and t1.id = ${gradeId}",
                        "</when>",
                        "AND t1.`status` = 1 ",
                        "AND t1.deleted = 0 ",
                        "AND t1.is_seal = 0 ",
                      "AND t1.position_num &gt; 0 ",
                      "AND t1.full_count &gt; 0 ",
                    "GROUP BY",
                      "t1.id,",
                      "t2.student_id ",
                    ") AS t ",
                    "GROUP BY",
                    "id ",
                    "ORDER BY",
                    "position_num ASC",
            "</script>"})
    public abstract List<Grade> getSchedulingGrade(@Param("yearPart") Integer yearPart,
                                              @Param("quarterNum") Integer quarterNum,
                                              @Param("schoolId") Long schoolId,
                                              @Param("subjectsId") Long subjectsId,
                                              @Param("yearClassId") Integer yearClassId,
                                              @Param("lessonType") Integer lessonType,
                                              @Param("classTypeId") Long classTypeId,
                                              @Param("gradeId") Long gradeId,
                                              @Param("getType") Integer getType
                                              );


    /**
     *
     * @param yearPart 年份
     * @param quarterNum 学期
     * @param schoolId 校区
     * @param subjectsId 科目
     * @param yearClassId 年级
     * @param lessonType
     * @param studentId 学生
     * @param weekDates 周几
     * @param timeStart 开始时间
     * @param timeEnd 结束结束
     * @return
     */
    @Select({"<script>",
            "SELECT",
                      "t2.*,",
                      "(",
                      "SELECT",
                        "count( id ) AS count ",
                      "FROM",
                        "student_grade AS t3 ",
                      "WHERE",
                        "t3.grade_id = t2.id ",
                        "AND t3.deleted = 0 ",
                        "AND t3.`status` &lt;&gt; 3 ",
                        "AND t3.reading_status IN ( 0, 1 )) AS allot_student_count ",
                    "FROM",
                      "grade AS t2 ",
                    "WHERE",
                      "t2.year_part = ${yearPart} ",
                      "AND t2.quarter_num = ${quarterNum}",
                      "AND t2.subjects_id = ${subjectsId} ",
                      "AND t2.lesson_type = ${lessonType} ",
                      "AND t2.school_id = ${schoolId} ",
                      "AND t2.week_dates IN ( ${weekDates} ) ",
                      "AND t2.time_start = #{timeStart} ",
                      "AND t2.time_end = #{timeEnd} ",
                      "AND t2.status = 1 ",
                      "AND t2.is_rebrush = 0 ",
                        "<when test='weekType == 1'>",
                              "AND t2.week_dates NOT IN (",
                              "SELECT",
                                "g.week_dates ",
                              "FROM",
                                "student_grade AS sg",
                                "LEFT JOIN grade AS g ON sg.grade_id = g.id",
                              "WHERE",
                                "sg.year_part = ${yearPart} ",
                                "AND sg.quarter_num = ${quarterNum} ",
                                "AND sg.lesson_type IN ( 1,2,3 ) ",//排除已经排的周几
                                "AND sg.student_id = ${studentId} ",
                                "AND sg.deleted = 0 ",
                                "AND sg.`status` &lt;&gt; 3 ",
                                "AND sg.reading_status IN ( 0, 1 )",
                              ") ",
                        "</when>",
                    "HAVING",
                      "((",
                          "full_count = 0 ",
                          ") ",
                      "OR ( allot_student_count &lt; full_count )) ",
                    "ORDER BY",
                      "week_dates ASC",
            "</script>"})
    public List<Grade> getSchdulingGrade(@Param("yearPart") Integer yearPart,
                                         @Param("quarterNum") Integer quarterNum,
                                         @Param("schoolId") Long schoolId,
                                         @Param("subjectsId") Long subjectsId,
                                         @Param("yearClassId") Integer yearClassId,
                                         @Param("lessonType") Integer lessonType,
                                         @Param("studentId") Long studentId,
                                         @Param("weekDates") String weekDates,
                                         @Param("timeStart") String timeStart,
                                         @Param("timeEnd") String timeEnd,
                                         @Param("weekType") Integer weekType
                                         );
}
